<?php

	class risposta {
		
		private $pdo;
	
		function __construct($conn)
		{
			// Create a connection to the database.
			$this->pdo = $conn;
		}
		
		
		function getResp($username, $num_slide){
			
			$this->pdo->beginTransaction();
			
			$stmt = $this->pdo->prepare("SELECT * FROM RISPOSTA 
			 WHERE  
				username = '".$username."' AND 
				num_slide = ".$num_slide);
			$stmt->execute();
			
			$this->pdo->commit();
			
			return $stmt->fetchAll();
		}
		
		function form2db($risp){
			return ($risp=="on" ? 1 : 0);
		}
		
		function saveResp($username) {
			$success = true;
			$msg = "";
			
			try {
				$page = $_POST["page"];
				
				$risp1 = $this->form2db($_POST["risp1"]);
				$risp2 = $this->form2db($_POST["risp2"]);
				$risp3 = $this->form2db($_POST["risp3"]);
				$risp4 = $this->form2db($_POST["risp4"]);
				$risp5 = $this->form2db($_POST["risp5"]);
				
				$nota = $_POST["nota"];
				
				$this->pdo->beginTransaction();
			
				$stmt = $this->pdo->prepare("INSERT INTO RISPOSTA 
				(username, num_slide, risp1, risp2, risp3, risp4, risp5, nota) 
				VALUES (?, ?, ?, ?, ?, ?, ?, ?) ".
				" ON DUPLICATE KEY UPDATE 
					risp1=".$risp1.", risp2=".$risp2.
						", risp3=".$risp3.", risp4=".$risp4.", risp5=".$risp5." ,nota='".$nota."'");
				
				$stmt->execute( array($username, $page, 
				$risp1, $risp2, $risp3, $risp4, $risp5, $nota));
				
				$this->pdo->commit();
				
			}catch (Exception $e) {
				$success = false;
				$msg = $e->getMessage();
			}
			
			$o = array("success"=>$success, "msg"=>$msg);
			
			return $o;
		}
		
		function listNote(){
			$this->pdo->beginTransaction();
			
			$stmt = $this->pdo->prepare(
				"SELECT r.*, u.* FROM pathways.RISPOSTA r, pathways.UTENTE u
					where r.username = u.username and nota <> ''
					order by num_slide;"
			);
			$stmt->execute();
			
			$this->pdo->commit();
			
			return $stmt->fetchAll();
		}
		
		
		function getStatistiche(){
			$this->pdo->beginTransaction();
			
			$stmt = $this->pdo->prepare(
			"SELECT  num_slide,
			    FLOOR( (sum(risp1) / count(distinct username)) * 100) as ris1,
			    FLOOR( (sum(risp2) / count(distinct username)) * 100) as ris2,
			    FLOOR( (sum(risp3) / count(distinct username)) * 100) as ris3,
			    FLOOR( (sum(risp4) / count(distinct username)) * 100) as ris4,
			    FLOOR( (sum(risp5) / count(distinct username)) * 100) as ris5
					
			    FROM RISPOSTA group by num_slide;"
			);
			$stmt->execute();
			
			$this->pdo->commit();
			
			return $stmt->fetchAll();
			
		}
		
		
	}
?>